package com.just.springjpa.web;

import com.just.springjpa.dao.CourseScoreRepository;
import com.just.springjpa.dao.PersonRepository;
import com.just.springjpa.dao.PersonRepository2;
import com.just.springjpa.dao.StudentRepository;
import com.just.springjpa.domain.CourseScore;
import com.just.springjpa.domain.Person;
import com.just.springjpa.domain.Student;
import com.just.springjpa.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.web.PageableDefault;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.persistence.criteria.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class DataController {
    //Spring data jpa已经自动注册bean，可以直接自动注入
    @Autowired
    PersonRepository personRepository;

    @Autowired
    PersonRepository2 personRepository2;

    @Autowired
    private StudentRepository studentRepository;

    @Autowired
    private CourseScoreRepository courseScoreRepository;

    @Autowired
    private StudentService studentService;

    /**
     * jpa自带的方法，可以直接保存
     * 保存单个，多个，根据id查找,id列表查找，查找所有，判断是否存在，计算总数，根据id删除，删除对象，删除一堆对象，删除所有等等，
     * 自带很多实现，不需要我们单独写了
     *     <S extends T> S save(S var1);
     *
     *     <S extends T> Iterable<S> saveAll(Iterable<S> var1);
     *
     *     Optional<T> findById(ID var1);
     *
     *     boolean existsById(ID var1);
     *
     *     Iterable<T> findAll();
     *
     *     Iterable<T> findAllById(Iterable<ID> var1);
     *
     *     long count();
     *
     *     void deleteById(ID var1);
     *
     *     void delete(T var1);
     *
     *     void deleteAll(Iterable<? extends T> var1);
     *
     *     void deleteAll();
     *
     */
    @RequestMapping("/save")
    public Person save(String name,String address,Integer age){
       Person person=new Person(null,name,age,address);
       return personRepository.save(person);
    }
    @RequestMapping("/q1")
    public List<Person> findByAddress(String address){
       List<Person> people=personRepository.findByAddress(address);
       return people;
    }
    @RequestMapping("/q2")
    public Person findByNameAndAddress(String name,String address){
        Person person=personRepository.findByNameAndAddress(name,address);
        return person;
    }
    @RequestMapping("/q3")
    public Person withNameAndAddressQuery(String name,String address){
        Person person=personRepository.withNameAndAddressQuery(name,address);
        return person;
    }
    @RequestMapping("/q4")
    public List<Person> withNameAndAddressNamedQuery(String name,String address){
        List<Person> people=personRepository.withNameAndAddressNamedQuery(name,address);
        return people;
    }
    //测试排序
    @RequestMapping("/sort")
    public List<Person> sort(){
        List<Person> people=personRepository.findAll(new Sort(Sort.Direction.ASC,"age"));
        return people;
    }
    //测试分页
    @RequestMapping("/page")
    public Page<Person> page(Pageable pageable){
        Page<Person> personPage=personRepository.findAll(pageable);
        return personPage;
    }
    @GetMapping("/auto")
    public Page<Person> auto(Person person,Pageable pageable){
        Page<Person> people=personRepository2.findByAuto(person,pageable);
        return people;
    }
    @GetMapping("/q5")
    public List<Person> findByMyQuery(String name){
        List<Person> people=personRepository.findByMyQuery(name);
        return people;
    }
    @RequestMapping("/update")
    public Integer updatePerson(Long id,int age){
        return personRepository.updatePersonAge(id,age);
    }

    @RequestMapping("/delete")
    public Integer deleteByAge(int age){
        return personRepository.deleteByAge(age);
    }

    /**
     * 根据学生姓名和最低分数查询
     * @param name 学生姓名，可为空
     * @param minScore 最低分数，可为空
     * @param pageable 分页参数
     * @return 查询到的分数结果
     */
    @GetMapping("/getScore")
    public Page<CourseScore> getScore(@RequestParam(required = false) String name,
                                      @RequestParam(required = false) BigDecimal minScore,
                                      @PageableDefault(size = 20) Pageable pageable){
        //关联动态查询
        Page<CourseScore> courseScores=courseScoreRepository.findAll(new Specification<CourseScore>() {
            @Override
            public Predicate toPredicate(Root<CourseScore> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicatesList = new ArrayList<>();
                //join里面的参数写法有好几种，这里采用最简单的一种，直接写属性名，关联的Student,内联
                Join<Student,CourseScore> join=root.join("student",JoinType.INNER);
                //注意name属性是在Student里面的，而join里面比较的属性都是关联的那张表里的字段，用join来获取
                if(!StringUtils.isEmpty(name)){
//                    predicatesList.add(
//                            criteriaBuilder.equal(
//                                    join.get("name"), name
//                            )
//                    );
                    predicatesList.add(
                            criteriaBuilder.like(
                                    join.get("name"), name
                            )
                    );
                }
                //分数是CourseScore里的属性，用root获取
                if(minScore!=null){
                    predicatesList.add(
                            criteriaBuilder.greaterThanOrEqualTo(
                                    root.get("score"), minScore
                            )
                    );
                }
                return criteriaBuilder.and(predicatesList.toArray(new Predicate[0]));
            }
        },pageable);
        return courseScores;
    }

    @GetMapping(value = "/students")
    public Page<Student> findStudents(@RequestParam(required = false) String name,
                                      @RequestParam(required = false) Integer age,
                                      Pageable pageable){
        StringBuilder sb=new StringBuilder("select s from Student s  where 1=1 ");
        Map<String,Object> paramsMap=new HashMap<>();
        if(!StringUtils.isEmpty(name)){
            sb.append(" and s.name=:name");
            paramsMap.put("name",name);
        }
        if(age!=null){
            sb.append(" and s.age=:age");
            paramsMap.put("age",age);
        }
        return studentService.findByJPQL(sb.toString(),paramsMap,pageable);
    }
}
